import datetime
from django.core.serializers import serialize
from django.shortcuts import render, redirect
from django.http import JsonResponse, HttpResponse
import json
from app import models
from app.models import Kfv, Dwy, Kfc, Ph,Hma,Lz,Ddl
from django.views.decorators.csrf import csrf_exempt
from openpyxl import load_workbook
from django.core.paginator import Paginator  # 使用 Django 内置的 Paginator
from app.views.func import generate_form_data, save_form_data, generate_pagination_html, get_merged_queryset, \
    get_paginated_data, TYPE_HANDLERS
import os
from django.db.models import Q  # 添加Q对象的导入


def PH(request):
    if request.method == 'GET':
        context = generate_form_data(request, 'PH')
        return render(request, 'PH/PH.html', context)
    return save_form_data(request, Ph, 'PH')


@csrf_exempt
def hma(request):
    if request.method == 'GET':
        context = generate_form_data(request, 'HMA')
        return render(request, 'HMA/HMA.html', context)
    return save_form_data(request, Hma, 'HMA')

@csrf_exempt
def kfv(request):
    if request.method == 'GET':
        context = generate_form_data(request, 'KFV')
        return render(request, 'KFV/KFV_yb.html', context)
    return save_form_data(request, Kfv, 'KFV')

@csrf_exempt
def kfc(request):
    if request.method == 'GET':
        context = generate_form_data(request, 'KFC')
        return render(request, 'KFC/KFC_yb.html', context)
    return save_form_data(request, Kfc, 'KFC')

@csrf_exempt
def lz(request):
    if request.method == 'GET':
        context = generate_form_data(request, 'LZ')
        return render(request, 'LZ/LZ.html', context)
    return save_form_data(request, Lz, 'LZ')

@csrf_exempt
def ddl(request):
    if request.method == 'GET':
        context = generate_form_data(request, 'DDL')
        return render(request, 'DDL/DDL.html', context)
    return save_form_data(request, Ddl, 'DDL')


def tools(request):
    selected_value = request.GET.get('value')
    query_set = models.ToolInfo.objects.filter(tool_number=selected_value).first()
    if query_set is None:
        data_to_return = []
    else:
        data_to_return = json.loads(serialize('json', [query_set]))  # 示例数据
    return JsonResponse({'datalist': data_to_return})

@csrf_exempt
def add(request):
    if request.method == 'POST':
        data = request.POST.items()
        return JsonResponse({'status': True})

    return render(request, 'add.html')

def cal_list(request):
    # 查询本账户提交的所有数据
    user_submitted_data = get_merged_queryset(request)
    # 查询被拒绝的数据
    reject_obj = get_merged_queryset(request, {'approve': 3})

    # 分页处理
    page_object = get_paginated_data(request, user_submitted_data)
    reject_page_object = get_paginated_data(request, reject_obj)

    context = {
        'user_submitted_data': page_object,
        'page_string': generate_pagination_html(page_object.paginator, page_object.number),
        'reject_obj': reject_page_object,
        'page_string_no_syn': generate_pagination_html(reject_page_object.paginator, reject_page_object.number)
    }
    return render(request, 'list.html', context)

#
def list_syn(request, type, pk):
    new_mysql_obj = Dwy()
    obj = models.Dwy.objects.filter(id=pk).first()
    if obj is None:
        return redirect('/list/', {'status': False, 'message': '对象不存在'})
    for field in new_mysql_obj._meta.fields:
        field_name = field.name
        field_value = getattr(obj, field_name)
        setattr(new_mysql_obj, field_name, field_value)

    new_mysql_obj.save(using='mysql')
    obj.status = 1
    obj.save()

    return redirect('/list/', {'status': True, 'message': '数据同步成功！'})

def list_view(request, type, pk):
    handler = TYPE_HANDLERS.get(type)
    if not handler:
        return JsonResponse({'status': False, 'message': 'Invalid type'}, status=400)

    obj = handler['model'].objects.filter(id=pk).first()
    if not obj:
        return JsonResponse({'status': False, 'message': 'Object not found'}, status=404)

    context = {'obj': obj.all_data}
    return render(request, handler['view_template'], context)

# # 一键生成报告
# def report(request, type, pk):
#     # 1. 获取类型处理器
#     handler = TYPE_HANDLERS.get(type)
#     if not handler:
#         return redirect('/approve/', {'status': True, 'message': '数据同步成功！'})

#     # 2. 根据主键获取证书对象
#     obj = handler['model'].objects.filter(id=pk).first()
#     if not obj:
#         return redirect('/approve/', {'status': False, 'message': '对象不存在'})
    
#     try:
#         # 3. 处理发布日期参数
#         publish_date = request.GET.get('publish_date')
#         if publish_date:
#             try:
#                 # 如果是8位数字格式(如20250805)，转换为标准格式
#                 if len(publish_date) == 8 and publish_date.isdigit():
#                     publish_date = f"{publish_date[:4]}-{publish_date[4:6]}-{publish_date[6:8]}"
#             except Exception as e:
#                 pass
        
#         # 4. 获取证书的所有数据
#         all_data = obj.all_data if hasattr(obj, 'all_data') else None
#         if not isinstance(all_data, dict) or all_data is None:
#             raise ValueError("对象数据必须是字典类型且不能为None")
            
#         # 5. 加载Excel模板
#         workbook = load_workbook(handler['report_template'])
#         worksheet = workbook.active
        
#         # 6. 获取字段映射配置
#         from app.views.config import FIELD_MAPPINGS
#         field_mappings = FIELD_MAPPINGS.get(type, {})

#         # 7. 查找发布日期单元格位置
#         publish_date_cell = None
#         for cell, field in field_mappings.items():
#             if field == 'publish_date_cell':
#                 publish_date_cell = cell
#                 break
        
#         # 8. 查找签名单元格位置
#         signature_cell = None
#         for cell, field in field_mappings.items():
#             if field == 'signature_cell':
#                 signature_cell = cell
#                 break

#         # 9. 根据字段映射填充Excel数据
#         for cell, field in field_mappings.items():
#             # 跳过特殊配置项
#             if field in ['publish_date_cell', 'signature_cell']:
#                 continue
                
#             # 获取字段值
#             field_value = ''
            
#             # 首先从主数据中查找
#             if field in all_data:
#                 field_value = all_data.get(field, '')
#             # 如果在sensor中查找（针对DWY类型）
#             elif type == 'DWY' and 'sensor' in all_data and all_data['sensor']:
#                 # 对于DWY，我们通常只需要第一个电极的数据
#                 if isinstance(all_data['sensor'], list) and len(all_data['sensor']) > 0:
#                     sensor_data = all_data['sensor'][0]
#                     if field in sensor_data:
#                         field_value = sensor_data.get(field, '')
#             # 如果在ddg中查找（针对DWY类型）
#             elif type == 'DWY' and 'ddg' in all_data and all_data['ddg']:
#                 # 对于DWY，我们通常只需要第一个滴定管的数据
#                 if isinstance(all_data['ddg'], list) and len(all_data['ddg']) > 0:
#                     ddg_data = all_data['ddg'][0]
#                     if field in ddg_data:
#                         field_value = ddg_data.get(field, '')
            
#             worksheet[cell] = field_value

        
#         # 10. 添加发布日期到对应单元格
#         if publish_date and publish_date_cell:
#             worksheet[publish_date_cell] = publish_date
        
#         # 11. 添加签名图片到对应单元格
#         img_path = ".\\templates\\sign\\" + obj.user + ".png"
#         try:
#             from openpyxl.drawing.image import Image
#             if os.path.isfile(img_path):
#                 img = Image(img_path)
#                 img.width, img.height = (65, 33)
#                 # 使用配置的位置或默认位置
#                 signature_position = signature_cell if signature_cell else 'BD38'
#                 worksheet.add_image(img, signature_position)
#         except Exception as e:
#             print(f"添加签名图片失败: {e}")

#         # 12. 返回Excel文件
#         response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
#         response['Content-Disposition'] = f'attachment; filename="{obj.c_number}.xlsx"'
#         workbook.save(response)
#         return response
#     except Exception as e:
#         import traceback
#         traceback.print_exc()
#         return redirect('/approve/', {'status': False, 'message': '处理数据时发生错误'})
    
# 证书审批
def approve(request):
    # 获取所有未审批状态的报告
    queryset = []
    for type_name, handler in TYPE_HANDLERS.items():
        if 'model' in handler:
            # 只获取未审批的报告 (approve=1)
            queryset.extend(list(handler['model'].objects.filter(approve=1)))
    
    # 添加一个辅助函数来处理不同类型的日期
    def get_date_for_sorting(obj):
        date_value = getattr(obj, 'date', None)
        if date_value is None:
            return datetime.datetime.min
        if isinstance(date_value, str):
            # 尝试解析字符串格式的日期
            try:
                # 支持多种日期格式
                if len(date_value) == 8 and date_value.isdigit():  # YYYYMMDD
                    return datetime.datetime.strptime(date_value, '%Y%m%d')
                else:  # 尝试标准格式
                    return datetime.datetime.strptime(date_value, '%Y-%m-%d')
            except ValueError:
                return datetime.datetime.min
        elif isinstance(date_value, datetime.date) and not isinstance(date_value, datetime.datetime):
            # 如果是 date 类型，转换为 datetime
            return datetime.datetime.combine(date_value, datetime.datetime.min.time())
        elif isinstance(date_value, datetime.datetime):
            return date_value
        else:
            return datetime.datetime.min
    
    # 修改排序逻辑，按时间字段排序，确保所有数据混合按时间排序
    queryset.sort(key=get_date_for_sorting, reverse=True)
    page_object = get_paginated_data(request, queryset)
    return render(request, 'list_approve.html', {
        'queryset': page_object,
        'page_string': generate_pagination_html(page_object.paginator, page_object.number)
    })

@csrf_exempt
def approve_view(request, type, pk):
    handler = TYPE_HANDLERS.get(type)
    if not handler:
        return JsonResponse({'status': False, 'message': 'Invalid type'}, status=400)

    if request.method == 'GET':
        obj = handler['model'].objects.filter(id=pk).first()
        obj.all_data['inspector_Users']=request.unicom_full_name
        obj.save()
        if not obj:
            return JsonResponse({'status': False, 'message': 'Object not found'}, status=404)

        context = {'obj': obj.all_data, 'id': pk,"windows_user":request.unicom_full_name}

        # 添加证书URL验证到审批视图
        if request.user.is_authenticated:
            user_info = models.UserInfo.objects.filter(username=request.user.username).first()
            if user_info:
                context['cert_url'] = getattr(user_info, f'{type.lower()}_cert_url', None)

        return render(request, handler['approve_template'], context)

    obj = handler['model'].objects.filter(id=pk).first()
    
    if not obj:
        return JsonResponse({'status': False, 'message': 'Object not found'}, status=404)

    if request.POST.get('DropUserStatus') == '2':
        obj.approve = 2
        obj.save()
    elif request.POST.get('DropUserStatus') == '3':
        obj.approve = 3
        obj.save()
    elif request.POST.get('DropUserStatus') == '4':
        obj.approve = 4
        obj.save()
    return redirect('/approve/')

def list_reject(request, type, pk):
    handler = TYPE_HANDLERS.get(type)
    if not handler:
        return JsonResponse({'status': False, 'message': 'Invalid type'}, status=400)

    if request.method == 'GET':
        tools = models.ToolInfo.objects.all()
        obj = handler['model'].objects.filter(id=pk).first()
        if not obj:
            return JsonResponse({'status': False, 'message': 'Object not found'}, status=404)
        return render(request, handler['reject_template'], {'obj': obj.all_data, 'id': pk, 'tools': tools})

    c_number = request.POST.get('A1PH_Received02')
    # 查找是否有相同编号的记录
    existing_record = handler['model'].objects.filter(
        c_number=c_number
    ).order_by('-version').first()
    if existing_record:
        version = existing_record.version + 1
    else:
        version = 1
    data = request.POST.dict()
    new_data = handler['models'](all_data=data, c_number=request.POST.get('A1PH_Received02'),
                                 date=request.POST.get('A1PH_Rq07'),
                                 customer=request.POST.get('A1PH_Zsdw53'),
                                 snr=request.POST.get('A1PH_SN04'), user=request.unicom_username, type=type,
                                 version=version, approve=1)
    new_data.save()

    # 新增代码：更新其他同c_number证书的状态为4
    handler['model'].objects.filter(
        c_number=c_number
    ).exclude(
        id=new_data.id
    ).update(approve=4)

    return redirect('/list/')

# 在文件顶部添加类型映射

# 证书下载
def download(request):
    # 获取所有可能的查询参数
    query_params = {
        'c_number': request.GET.get('c_number', '').strip(),
        'approve': request.GET.get('approve', ''),
        'type': request.GET.get('type', ''), 
        'customer': request.GET.get('customer', '').strip(),
        'calibrator': request.GET.get('calibrator', '').strip(),
        'start_date': request.GET.get('start_date'),
        'end_date': request.GET.get('end_date')
    }

    # 构建动态查询条件
    base_filters = {}
    
    # 字段查询
    if query_params['c_number']:
        base_filters['c_number'] = query_params['c_number']
    if query_params['customer']:
        base_filters['customer__icontains'] = query_params['customer']
    if query_params['calibrator']:
        base_filters['all_data__calibrator__icontains'] = query_params['calibrator']  # 注意字段映射
    if query_params['approve']:
        base_filters['approve'] = query_params['approve']
    if query_params['type']:
        base_filters['type'] = query_params['type']
    
    # # 日期范围查询
    # if query_params['start_date'] and query_params['end_date']:
    #     try:
    #         # 将 YYYY-MM-DD 转换为 YYYYMMDD
    #         start_date = query_params['start_date'].replace("-", "")
    #         end_date = query_params['end_date'].replace("-", "")
    #         base_filters['date__range'] = [start_date, end_date]
    #     except Exception as e:
    #         print(f"日期格式转换错误: {e}")

    # 获取数据（优化查询效率）
    queryset = []
    for type_name, handler in TYPE_HANDLERS.items():
        if 'model' in handler:
            try:
                if base_filters:
                    qs = handler['model'].objects.filter(**base_filters)
                else:
                    qs = handler['model'].objects.all()
                
                items = list(qs)
                queryset.extend(items)
                
            except Exception as e:
                print(f"查询{type_name}类型数据时出错: {str(e)}")
                continue
    
    # 添加一个辅助函数来处理不同类型的日期
    def get_date_for_sorting(obj):
        date_value = getattr(obj, 'date', None)
        if date_value is None:
            return datetime.datetime.min
        if isinstance(date_value, str):
            # 尝试解析字符串格式的日期
            try:
                # 支持多种日期格式
                if len(date_value) == 8 and date_value.isdigit():  # YYYYMMDD
                    return datetime.datetime.strptime(date_value, '%Y%m%d')
                else:  # 尝试标准格式
                    return datetime.datetime.strptime(date_value, '%Y-%m-%d')
            except ValueError:
                return datetime.datetime.min
        elif isinstance(date_value, datetime.date) and not isinstance(date_value, datetime.datetime):
            # 如果是 date 类型，转换为 datetime
            return datetime.datetime.combine(date_value, datetime.datetime.min.time())
        elif isinstance(date_value, datetime.datetime):
            return date_value
        else:
            return datetime.datetime.min
    
    # 修改排序逻辑，按时间字段排序，确保所有数据混合按时间排序
    queryset.sort(key=get_date_for_sorting, reverse=True)
    page_object = get_paginated_data(request, queryset)
    
    # 添加日期默认值
    today = datetime.date.today()
    three_months_ago = today - datetime.timedelta(days=90)
    
    # 返回上下文（包含所有查询参数）
    context = {
        'queryset': page_object,
        'page_string': generate_pagination_html(page_object.paginator, page_object.number),
        'search_params': query_params,  # 传回所有查询参数
        'today': today,
        'three_months_ago': three_months_ago  # 添加这个变量
    }
    return render(request, 'download.html', context)

# 一键生成报告
def report(request, type, pk):
    # 1. 获取类型处理器
    handler = TYPE_HANDLERS.get(type)
    if not handler:
        return redirect('/approve/', {'status': True, 'message': '数据同步成功！'})

    # 2. 根据主键获取证书对象
    obj = handler['model'].objects.filter(id=pk).first()
    if not obj:
        return redirect('/approve/', {'status': False, 'message': '对象不存在'})
    
    # 检查是否需要使用图标版本
    icon_version = request.GET.get('icon_version', '').lower() == 'true'
    
    try:
        # 3. 处理发布日期参数
        publish_date = request.GET.get('publish_date')
        if publish_date:
            try:
                # 如果是8位数字格式(如20250805)，转换为标准格式
                if len(publish_date) == 8 and publish_date.isdigit():
                    publish_date = f"{publish_date[:4]}-{publish_date[4:6]}-{publish_date[6:8]}"
            except Exception as e:
                pass
        
        # 4. 获取证书的所有数据
        all_data = obj.all_data if hasattr(obj, 'all_data') else None
        if not isinstance(all_data, dict) or all_data is None:
            raise ValueError("对象数据必须是字典类型且不能为None")
            
        # 5. 加载Excel模板 - 根据是否为图标版本选择不同的模板
        template_path = handler['report_template']
        if icon_version:
            # 如果是图标版本，将文件名中的 .xlsx 替换为 _icon.xlsx
            template_path = template_path.replace('.xlsx', '_icon.xlsx')
            
        workbook = load_workbook(template_path)
        worksheet = workbook.active
        
        # 6. 获取字段映射配置
        from app.views.config import FIELD_MAPPINGS
        field_mappings = FIELD_MAPPINGS.get(type, {})

        # 7. 查找发布日期单元格位置
        publish_date_cell = None
        for cell, field in field_mappings.items():
            if field == 'publish_date_cell':
                publish_date_cell = cell
                break
        
        # 8. 查找签名单元格位置
        signature_cell = None
        for cell, field in field_mappings.items():
            if field == 'signature_cell':
                signature_cell = cell
                break

        # 9. 根据字段映射填充Excel数据
        for cell, field in field_mappings.items():
            # 跳过特殊配置项
            if field in ['publish_date_cell', 'signature_cell']:
                continue
                
            # 获取字段值
            field_value = ''
            
            # 首先从主数据中查找
            if field in all_data:
                field_value = all_data.get(field, '')
            # 如果在sensor中查找（针对DWY类型）
            elif type == 'DWY' and 'sensor' in all_data and all_data['sensor']:
                # 对于DWY，我们通常只需要第一个电极的数据
                if isinstance(all_data['sensor'], list) and len(all_data['sensor']) > 0:
                    sensor_data = all_data['sensor'][0]
                    if field in sensor_data:
                        field_value = sensor_data.get(field, '')
            # 如果在ddg中查找（针对DWY类型）
            elif type == 'DWY' and 'ddg' in all_data and all_data['ddg']:
                # 对于DWY，我们通常只需要第一个滴定管的数据
                if isinstance(all_data['ddg'], list) and len(all_data['ddg']) > 0:
                    ddg_data = all_data['ddg'][0]
                    if field in ddg_data:
                        field_value = ddg_data.get(field, '')
            
            worksheet[cell] = field_value

        
        # 10. 添加发布日期到对应单元格
        if publish_date and publish_date_cell:
            worksheet[publish_date_cell] = publish_date
        
        # 11. 添加签名图片到对应单元格
        img_path = ".\\templates\\sign\\" + obj.user + ".png"
        try:
            from openpyxl.drawing.image import Image
            if os.path.isfile(img_path):
                # 使用配置的位置或默认位置
                signature_position = signature_cell if signature_cell else 'BD38'
                if type=='HMA':
                    # 为HMA类型在两个位置添加签名图片
                    img1 = Image(img_path)
                    img1.width, img1.height = (65, 33)
                    worksheet.add_image(img1, signature_position)
                    
                    img2 = Image(img_path)
                    img2.width, img2.height = (65, 33)
                    worksheet.add_image(img2, 'BS194')
                else:
                    # 其他类型只在默认位置添加签名图片
                    img = Image(img_path)
                    img.width, img.height = (65, 33)
                    worksheet.add_image(img, signature_position)
        except Exception as e:
            print(f"添加签名图片失败: {e}")

        # 12. 返回Excel文件
        response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        # 根据是否为图标版本设置不同的文件名
        file_suffix = '_icon.xlsx' if icon_version else '.xlsx'
        # 优化中文文件名编码问题
        from urllib.parse import quote
        filename = f"{obj.c_number}_{obj.all_data['A1PH_ProdModel03']}_{obj.snr}_{obj.customer}{file_suffix}"
        # 对中文文件名进行编码以支持不同浏览器
        encoded_filename = quote(filename)
        response['Content-Disposition'] = f"attachment; filename*=UTF-8''{encoded_filename}"
        workbook.save(response)
        return response
    except Exception as e:
        import traceback
        traceback.print_exc()
        return redirect('/approve/', {'status': False, 'message': '处理数据时发生错误'})
    

def batch_report(request):
    """
    批量下载报告，生成压缩包
    """
    import zipfile
    from io import BytesIO
    
    # 获取要下载的证书信息列表，格式为 type:pk
    cert_info_list = request.GET.get('certs', '').split(',')
    cert_info_list = [info for info in cert_info_list if info.strip()]
    
    if not cert_info_list:
        return redirect('/approve/', {'status': False, 'message': '未选择任何证书'})
    
    # 创建内存中的ZIP文件
    zip_buffer = BytesIO()
    
    try:
        with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
            # 检查是否需要使用图标版本
            icon_version = request.GET.get('icon_version', '').lower() == 'true'
            
            # 处理发布日期参数
            publish_date = request.GET.get('publish_date')
            if publish_date:
                try:
                    # 如果是8位数字格式(如20250805)，转换为标准格式
                    if len(publish_date) == 8 and publish_date.isdigit():
                        publish_date = f"{publish_date[:4]}-{publish_date[4:6]}-{publish_date[6:8]}"
                except Exception as e:
                    pass
            
            # 处理每个证书
            for cert_info in cert_info_list:
                try:
                    # 解析证书信息 type:pk
                    cert_type, cert_id = cert_info.split(':')
                    
                    # 获取类型处理器
                    handler = TYPE_HANDLERS.get(cert_type)
                    if not handler:
                        continue
                    
                    # 获取证书对象
                    obj = handler['model'].objects.filter(id=cert_id).first()
                    if not obj:
                        continue
                    
                    # 获取证书的所有数据
                    all_data = obj.all_data if hasattr(obj, 'all_data') else None
                    if not isinstance(all_data, dict) or all_data is None:
                        continue
                    
                    # 加载Excel模板
                    template_path = handler['report_template']
                    if icon_version:
                        template_path = template_path.replace('.xlsx', '_icon.xlsx')
                    
                    workbook = load_workbook(template_path)
                    worksheet = workbook.active
                    
                    # 获取字段映射配置
                    from app.views.config import FIELD_MAPPINGS
                    field_mappings = FIELD_MAPPINGS.get(cert_type, {})
                    
                    # 查找发布日期单元格位置
                    publish_date_cell = None
                    for cell, field in field_mappings.items():
                        if field == 'publish_date_cell':
                            publish_date_cell = cell
                            break
                    
                    # 查找签名单元格位置
                    signature_cell = None
                    for cell, field in field_mappings.items():
                        if field == 'signature_cell':
                            signature_cell = cell
                            break
                    
                    # 根据字段映射填充Excel数据
                    for cell, field in field_mappings.items():
                        # 跳过特殊配置项
                        if field in ['publish_date_cell', 'signature_cell']:
                            continue
                            
                        # 获取字段值
                        field_value = ''
                        
                        # 首先从主数据中查找
                        if field in all_data:
                            field_value = all_data.get(field, '')
                        # 如果在sensor中查找（针对DWY类型）
                        elif cert_type == 'DWY' and 'sensor' in all_data and all_data['sensor']:
                            # 对于DWY，我们通常只需要第一个电极的数据
                            if isinstance(all_data['sensor'], list) and len(all_data['sensor']) > 0:
                                sensor_data = all_data['sensor'][0]
                                if field in sensor_data:
                                    field_value = sensor_data.get(field, '')
                        # 如果在ddg中查找（针对DWY类型）
                        elif cert_type == 'DWY' and 'ddg' in all_data and all_data['ddg']:
                            # 对于DWY，我们通常只需要第一个滴定管的数据
                            if isinstance(all_data['ddg'], list) and len(all_data['ddg']) > 0:
                                ddg_data = all_data['ddg'][0]
                                if field in ddg_data:
                                    field_value = ddg_data.get(field, '')
                        
                        worksheet[cell] = field_value
                    
                    # 添加发布日期到对应单元格
                    if publish_date and publish_date_cell:
                        worksheet[publish_date_cell] = publish_date
                    
                    # 添加签名图片到对应单元格
                    img_path = ".\\templates\\sign\\" + obj.user + ".png"
                    try:
                        from openpyxl.drawing.image import Image
                        if os.path.isfile(img_path):
                            # 使用配置的位置或默认位置
                            signature_position = signature_cell if signature_cell else 'BD38'
                            if cert_type == 'HMA':
                                # 为HMA类型在两个位置添加签名图片
                                img1 = Image(img_path)
                                img1.width, img1.height = (65, 33)
                                worksheet.add_image(img1, signature_position)
                                
                                img2 = Image(img_path)
                                img2.width, img2.height = (65, 33)
                                worksheet.add_image(img2, 'BS194')
                            else:
                                # 其他类型只在默认位置添加签名图片
                                img = Image(img_path)
                                img.width, img.height = (65, 33)
                                worksheet.add_image(img, signature_position)
                    except Exception as e:
                        print(f"添加签名图片失败: {e}")
                    
                    # 将Excel文件写入内存
                    excel_buffer = BytesIO()
                    workbook.save(excel_buffer)
                    excel_buffer.seek(0)
                    
                    # 添加到ZIP文件中
                    file_suffix = '_icon.xlsx' if icon_version else '.xlsx'
                    filename = f"{obj.c_number}_{obj.all_data['A1PH_ProdModel03']}_{obj.snr}_{obj.customer}{file_suffix}"
                    zip_file.writestr(filename, excel_buffer.getvalue())
                    
                except Exception as e:
                    print(f"处理证书 {cert_info} 时出错: {e}")
                    continue
        
        # 返回ZIP文件
        zip_buffer.seek(0)
        response = HttpResponse(zip_buffer.getvalue(), content_type='application/zip')
        from urllib.parse import quote
        zip_filename = "certificates.zip"
        encoded_filename = quote(zip_filename)
        response['Content-Disposition'] = f'attachment; filename*=UTF-8\'\'{encoded_filename}'
        return response
        
    except Exception as e:
        import traceback
        traceback.print_exc()
        return redirect('/approve/', {'status': False, 'message': '处理批量下载时发生错误'})

def certificate_stats(request):
    """证书统计页面"""
    from django.db.models import Count
    from collections import defaultdict
    import datetime
    
    # 获取所有证书数据
    stats_data = {}
    
    # 按证书类型统计
    type_stats = defaultdict(lambda: {'total': 0, 'approved': 0, 'pending': 0, 'rejected': 0, 'cancelled': 0})
    
    # 按月份统计
    monthly_stats = defaultdict(lambda: {'total': 0, 'approved': 0})
    
    # 按用户统计
    user_stats = defaultdict(lambda: {'total': 0, 'approved': 0, 'pending': 0, 'rejected': 0, 'cancelled': 0})
    
    # 总体统计
    total_stats = {'total': 0, 'approved': 0, 'pending': 0, 'rejected': 0, 'cancelled': 0}
    
    # 获取所有用户信息，用于显示姓名
    user_info_dict = {}
    user_objects = models.UserInfo.objects.all()
    for user_obj in user_objects:
        user_info_dict[user_obj.username] = user_obj.full_name if user_obj.full_name else user_obj.username
    
    # 为用户统计添加各类型证书数量
    user_type_details = defaultdict(lambda: defaultdict(int))
    
    for type_name, handler in TYPE_HANDLERS.items():
        if 'model' in handler:
            queryset = handler['model'].objects.all()
            
            for cert in queryset:
                # 使用用户姓名替换用户名
                user_display_name = user_info_dict.get(cert.user, cert.user) if cert.user else "未知用户"
                
                # 类型统计
                type_stats[type_name]['total'] += 1
                total_stats['total'] += 1
                
                if cert.approve == 1:
                    type_stats[type_name]['pending'] += 1
                    total_stats['pending'] += 1
                elif cert.approve == 2:
                    type_stats[type_name]['approved'] += 1
                    total_stats['approved'] += 1
                elif cert.approve == 3:
                    type_stats[type_name]['rejected'] += 1
                    total_stats['rejected'] += 1
                elif cert.approve == 4:
                    type_stats[type_name]['cancelled'] += 1
                    total_stats['cancelled'] += 1
                
                # 月份统计
                try:
                    if cert.date:
                        if len(cert.date) == 8 and cert.date.isdigit():  # YYYYMMDD格式
                            year_month = f"{cert.date[:4]}-{cert.date[4:6]}"
                        else:  # 其他格式尝试解析
                            date_obj = datetime.datetime.strptime(cert.date[:7], '%Y-%m')
                            year_month = date_obj.strftime('%Y-%m')
                        
                        monthly_stats[year_month]['total'] += 1
                        if cert.approve == 2:
                            monthly_stats[year_month]['approved'] += 1
                except:
                    pass
                
                # 用户统计
                if cert.user:
                    user_stats[user_display_name]['total'] += 1
                    user_type_details[user_display_name][type_name] += 1
                    
                    if cert.approve == 1:
                        user_stats[user_display_name]['pending'] += 1
                    elif cert.approve == 2:
                        user_stats[user_display_name]['approved'] += 1
                    elif cert.approve == 3:
                        user_stats[user_display_name]['rejected'] += 1
                    elif cert.approve == 4:
                        user_stats[user_display_name]['cancelled'] += 1
    
    # 转换为普通字典并排序
    type_stats = dict(type_stats)
    monthly_stats = dict(sorted(monthly_stats.items(), reverse=True)[:12])  # 最近12个月
    
    # 处理搜索功能
    search_query = request.GET.get('search', '')
    if search_query:
        # 过滤包含搜索关键词的用户
        filtered_user_stats = {}
        for user, stats in user_stats.items():
            if search_query.lower() in user.lower():
                filtered_user_stats[user] = stats
        user_stats = filtered_user_stats
    
    user_stats = dict(sorted(user_stats.items(), key=lambda x: x[1]['total'], reverse=True)[:20])  # 前20用户
    
    # 修复用户类型详情数据结构 - 只保留有数据的用户，并转换为普通字典
    filtered_user_type_details = {}
    for user in user_stats.keys():
        if user in user_type_details:
            # 将 defaultdict 转换为普通字典，确保模板中的 items 过滤器能正常工作
            filtered_user_type_details[user] = dict(user_type_details[user])
    
    # 获取最近12个月的数据
    recent_stats = []
    today = datetime.date.today()
    for i in range(12):
        # 计算12个月前的日期
        if today.month - i <= 0:
            year = today.year - 1
            month = today.month - i + 12
        else:
            year = today.year
            month = today.month - i
        
        month_str = f"{year:04d}{month:02d}"
        month_display = f"{year}-{month:02d}"
        
        month_count = 0
        
        for type_name, handler in TYPE_HANDLERS.items():
            if 'model' in handler:
                # 查询该月份的所有证书（支持两种日期格式）
                month_count += handler['model'].objects.filter(
                    Q(date__startswith=month_str) | Q(date__startswith=month_display)
                ).count()
        
        recent_stats.append({
            'date': month_display,
            'count': month_count
        })
    
    recent_stats.reverse()  # 按时间正序
    

    
    context = {
        'total_stats': total_stats,
        'type_stats': type_stats,
        'monthly_stats': monthly_stats,
        'user_stats': user_stats,
        'recent_stats': recent_stats,
        'user_type_details': filtered_user_type_details,  # 用户各类型证书详情
        'search_query': search_query  # 搜索查询
    }
    
    return render(request, 'certificate_stats.html', context)